package net.hn.hnms.biz.plan.mapper;

import net.hn.hnms.biz.plan.vo.CoalMiningTotalVo;
import net.hn.hnms.biz.plan.vo.CoalMiningYoyMomVo;
import net.hn.hnms.biz.plan.vo.CoalTypeCategoryVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface CoalmineBigScreenMapper {




    @Select("<script>" +
            "   " +
            "\t\tSELECT org_code,org_full_name,org_short_name,\n" +
            "            <if test='type== 3'>" +
            " TO_CHAR(COALESCE(CAST(output_qty_yoy AS numeric), 0), 'FM9999999990.00') AS outputQtyYoy,\n" +
            "    TO_CHAR(COALESCE(CAST(sales_qty_yoy AS numeric), 0), 'FM9999999990.00') AS salesQtyYoy,\n" +
            "    TO_CHAR(COALESCE(CAST(footage_qty_yoy AS numeric), 0), 'FM9999999990.00') AS footageQtyYoy,\n" +
            "    TO_CHAR(COALESCE(CAST(peel_qty_yoy AS numeric), 0), 'FM9999999990.00') AS peelQtyYoy " +
            "            </if>" +
            "            <if test='type== 2'>" +
            "    TO_CHAR(COALESCE(CAST(output_qty_mom AS numeric), 0), 'FM9999999990.00') AS outputQtyMom,\n" +
            "    TO_CHAR(COALESCE(CAST(sales_qty_mom AS numeric), 0), 'FM9999999990.00') AS salesQtyMom,\n" +
            "    TO_CHAR(COALESCE(CAST(footage_qty_mom AS numeric), 0), 'FM9999999990.00') AS footageQtyMom,\n" +
            "    TO_CHAR(COALESCE(CAST(peel_qty_mom AS numeric), 0), 'FM9999999990.00') AS peelQtyMom,\n" +
            "   TO_CHAR(COALESCE(CAST(output_qty_yoy AS numeric), 0), 'FM9999999990.00') AS outputQtyYoy,\n" +
            "    TO_CHAR(COALESCE(CAST(sales_qty_yoy AS numeric), 0), 'FM9999999990.00') AS salesQtyYoy,\n" +
            "    TO_CHAR(COALESCE(CAST(footage_qty_yoy AS numeric), 0), 'FM9999999990.00') AS footageQtyYoy,\n" +
            "    TO_CHAR(COALESCE(CAST(peel_qty_yoy AS numeric), 0), 'FM9999999990.00') AS peelQtyYoy " +
            "            </if>" +
            "            <if test='type== 1'>" +
            "     TO_CHAR(COALESCE(CAST(output_qty_mom AS numeric), 0), 'FM9999999990.00') AS outputQtyMom,\n" +
            "    TO_CHAR(COALESCE(CAST(sales_qty_mom AS numeric), 0), 'FM9999999990.00') AS salesQtyMom,\n" +
            "    TO_CHAR(COALESCE(CAST(footage_qty_mom AS numeric), 0), 'FM9999999990.00') AS footageQtyMom,\n" +
            "    TO_CHAR(COALESCE(CAST(peel_qty_mom AS numeric), 0), 'FM9999999990.00') AS peelQtyMom,\n" +
            "    TO_CHAR(COALESCE(CAST(output_qty_yoy AS numeric), 0), 'FM9999999990.00') AS outputQtyYoy,\n" +
            "    TO_CHAR(COALESCE(CAST(sales_qty_yoy AS numeric), 0), 'FM9999999990.00') AS salesQtyYoy,\n" +
            "    TO_CHAR(COALESCE(CAST(footage_qty_yoy AS numeric), 0), 'FM9999999990.00') AS footageQtyYoy,\n" +
            "    TO_CHAR(COALESCE(CAST(peel_qty_yoy AS numeric), 0), 'FM9999999990.00') AS peelQtyYoy " +
            "            </if>" +

            " FROM " +
            "            <if test='type== 3'>" +
            "              dws_jygl_coal_mining_year_yoy_d_f    " +
            "            </if>" +
            "            <if test='type== 2'>" +
            "            dws_jygl_coal_mining_month_yoy_mom_d_f   " +
            "            </if>" +
            "            <if test='type== 1'>" +
            "            dws_jygl_coal_mining_daily_yoy_mom_d_f   " +
            "            </if>" +
            " WHERE  org_code = #{mineCode} " +


            "            <if test='mark == 1' >" +
            "    and dt = CURRENT_DATE - 1 " +
            "            </if>" +
            "            <if test='mark == 2' >" +
            "    and dt = CURRENT_DATE - 2 " +
            "            </if>" +
            "   " +

            "</script>")
    List<CoalMiningYoyMomVo> getSameRingCompare(@Param("type") Integer type, @Param("mark") Integer mark, @Param("mineCode") String  mineCode);


    @Select("<script>" +
            "    SELECT unit,ym,hm,mm,xhm,xjm,km,qt,lm,internaluse,supplyinternalpower,inventory     FROM jygl.ods_finedata_insert_coal_sales_daily_d_f\n" +
            "                where 1=1 " +
            "            <if test='mark == 1' >" +
            "    and dt = CURRENT_DATE - 1 " +
            "            </if>" +
            "            <if test='mark == 2' >" +
            "    and dt = CURRENT_DATE - 2 " +
            "            </if>" +
            " and daily_time = CURRENT_DATE - 1  " +
            " and unit = #{mineCode} " +
            "</script>")
    List<CoalTypeCategoryVo> getCoaltypecategoryByMineName(@Param("mark") Integer mark, @Param("mineCode") String  mineCode);


    @Select("<script>" +
            " SELECT\n" +
            "    info.mk_rank, info.mk_full_name,\n" +
            "    info.mk_short_name as orgShortName,\n" +
            "    info.mk_code,\n" +
            "    total.org_full_name AS orgFullName,\n" +
            //月报和年报用总计划
            "            <if test='type== 2 or type== 3'>" +
            "    ROUND(SUM(total.total_planned_output_qty) / 10000.0, 2) AS plannedOutputQty," +
            "    ROUND(SUM(total.total_planned_sales_qty) / 10000.0, 2) AS plannedSalesQty," +
            "    ROUND(SUM(total.total_planned_peel_qty) / 10000.0, 2) AS plannedPeelQty," +
            "    ROUND(SUM(total.total_planned_footage_qty), 2) AS plannedFootageQty," +
            "    ROUND(AVG(total.total_output_compl_rate) * 100, 2) AS outputComplRate,   \n" +
            "    ROUND(AVG(total.total_sales_compl_rate) * 100, 2) AS salesComplRate,     \n" +
            "    ROUND(AVG(total.total_footage_compl_rate) * 100, 2) AS footageComplRate,\n" +
            "    ROUND(AVG(total.total_peel_compl_rate) * 100, 2) AS peelComplRate,   " +
            "            </if>" +
            //日报还用进度计划字段
            "            <if test='type== 1'>" +
            "     TO_CHAR(ROUND(SUM(total.planned_output_qty) / 10000.0, 2), 'FM9999999990.00') AS plannedOutputQty,\n" +
            "    TO_CHAR(ROUND(SUM(total.planned_sales_qty) / 10000.0, 2), 'FM9999999990.00') AS plannedSalesQty,\n" +
            "    TO_CHAR(ROUND(SUM(total.planned_peel_qty) / 10000.0, 2), 'FM9999999990.00') AS plannedPeelQty,\n" +
            "    TO_CHAR(ROUND(SUM(total.planned_footage_qty), 2), 'FM9999999990.00') AS plannedFootageQty,\n" +
            "    TO_CHAR(ROUND(AVG(total.output_compl_rate) * 100, 2), 'FM9999999990.00') AS outputComplRate,\n" +
            "    TO_CHAR(ROUND(AVG(total.sales_compl_rate) * 100, 2), 'FM9999999990.00') AS salesComplRate,\n" +
            "    TO_CHAR(ROUND(AVG(total.footage_compl_rate) * 100, 2), 'FM9999999990.00') AS footageComplRate,\n" +
            "    TO_CHAR(ROUND(AVG(total.peel_compl_rate) * 100, 2), 'FM9999999990.00') AS peelComplRate,  " +
            "            </if>" +
            "    TO_CHAR(TRUNC(SUM(total.actual_output_qty) / 10000.0, 2), 'FM9999999990.00') AS actualOutputQty,\n" +
            "    TO_CHAR(TRUNC(SUM(total.actual_sales_qty) / 10000.0, 2), 'FM9999999990.00') AS actualSalesQty,\n" +
            "    TO_CHAR(TRUNC(SUM(total.actual_peel_qty) / 10000.0, 2), 'FM9999999990.00') AS actualPeelQty,\n" +
            "    TO_CHAR(TRUNC(SUM(total.actual_footage_qty), 2), 'FM9999999990.00') AS actualFootageQty " +
            "FROM\n" +
            "   \t\n" +
            "\t(SELECT mk_full_name,  mk_short_name,mk_rank,mk_code  FROM dim." +
            " " +
            " )  info\n" +
            "LEFT JOIN\n" +
            "            <if test='type== 1'>" +
            "    dm.dm_jygl_coal_mining_daily_total_d_f total ON info.mk_code = total.org_code\n" +
            "            </if>" +
            "            <if test='type== 2'>" +
            "    dm.dm_jygl_coal_mining_month_detail_d_f total ON info.mk_code = total.org_code\n" +
            "            </if>" +
            "            <if test='type== 3'>" +
            "    dm.dm_jygl_coal_mining_year_total_d_f total ON info.mk_code = total.org_code\n" +
            "            </if>" +
            "WHERE  1=1 \n" +
            "   and total.org_code = #{orgCode}  " +
            "            <if test='type== 1 and mark == 1' >" +
            "    and total.st_date <![CDATA[ = ]]>   current_date-1  " +
            "            </if>" +
            "            <if test='type== 1 and mark == 2' >" +
            "    and total.st_date <![CDATA[ = ]]>   current_date-2  " +
            "            </if>" +
            "            <if test='type== 2'>" +
            "      AND total.st_month = TO_CHAR(current_date-1, 'YYYY-MM') " +
            "            </if>" +
            "            <if test='type== 3'>" +
            "       AND total.st_year = TO_CHAR(current_date-1, 'YYYY') " +
            "            </if>" +
            "GROUP BY\n" +
            "    info.mk_rank,total.org_full_name,\n" +
            "    info.mk_full_name,\n" +
            "    info.mk_short_name,\n" +
            "    info.mk_code\n" +
            "ORDER BY\n" +
            "    info.mk_rank, total.org_full_name,\n" +
            "    info.mk_full_name,\n" +
            "    info.mk_short_name,\n" +
            "    info.mk_code; " +
            "</script>")
    CoalMiningTotalVo getDailyOutputStatusByOrgCode(@Param("type") Integer type, @Param("mark") Integer mark, @Param("orgCode") String orgCode);



    @Select("<script>" +
            "   WITH DATA AS (\n" +
            "                SELECT\n" +
            "                    st_year AS stYear,\n" +
            "             org_short_name AS org_short_name, \n" +
            "                    ROUND(SUM(actual_output_qty) / 10000.0, 2) AS actualOutputQty,\n" +
            "                    ROUND(SUM(actual_sales_qty) / 10000.0, 2) AS actualSalesQty," +
            "   TO_CHAR(ROUND(actual_footage_qty   , 2), 'FM9999999990.00') AS actualfootageqty  \n" +
            "                FROM\n" +
            "                    dm.dm_jygl_coal_mining_year_total_d_f\n" +
            "                WHERE\n" +
            "                    st_year::INTEGER  <![CDATA[ >= ]]> EXTRACT(YEAR FROM CURRENT_DATE) - 10\n" +
            "                    AND st_year::INTEGER  <![CDATA[ < ]]> EXTRACT(YEAR FROM CURRENT_DATE)\n" +
            "                        \n" +
            "                          and org_code = #{mineCode} " +
            "                GROUP BY\n" +
            "                    st_year,org_short_name\n" +
            "            )\n" +
            "            SELECT\n" +
            "                stYear,\n" +
            "                org_short_name,\n" +
            "                actualOutputQty,\n" +
            "                actualSalesQty," +
            "actualfootageqty \n" +
            "            FROM\n" +
            "                DATA\n" +
            "            ORDER BY\n" +
            "                stYear DESC; " +
            "</script>")
    List<CoalMiningTotalVo> recentlyTenYearsByIndustry(@Param("flag") Integer flag,@Param("mineCode") String mineCode);



    @Select("<script>" +

            "                WITH trend_data AS (\n" +
            "    SELECT \n" +
            "        to_char(date_trunc('month', CAST(st_month <![CDATA[ || ]]> '-01' AS date)), 'MM') AS st_date,\n" +
            "        to_char(date_trunc('month', CAST(st_month <![CDATA[ || ]]> '-01' AS date)), 'YYYY-MM') AS stYear,\n" +
            "        org_short_name AS org_short_name,\n" +
            "        ROUND(actual_output_qty / 10000.0, 2) AS actualOutputQty,\n" +
            "        ROUND(actual_sales_qty / 10000.0, 2) AS actualSalesQty,\n" +
            "        TO_CHAR(ROUND(actual_footage_qty, 2), 'FM9999999990.00') AS actualfootageqty,\n" +
            "        TO_CHAR(ROUND(planned_output_qty / 10000.0, 2), 'FM9999999990.00') AS plannedOutputQty,\n" +
            "        TO_CHAR(ROUND(planned_sales_qty / 10000.0, 2), 'FM9999999990.00') AS plannedSalesQty,\n" +
            "        TO_CHAR(ROUND(planned_footage_qty, 2), 'FM9999999990.00') AS plannedFootageQty\n" +
            "    FROM \n" +
            "        dm.dm_jygl_coal_mining_month_total_d_f\n" +
            "    WHERE \n" +
            "        EXTRACT(year FROM CAST(st_month <![CDATA[ || ]]> '-01' AS date)) = #{year}  " +
            "        AND CAST(st_month <![CDATA[ || ]]> '-01' AS date) <![CDATA[ < ]]> DATE_TRUNC('month', CURRENT_DATE)\n" +
            "        AND org_code = #{mineCode}\n" +
            ")\n" +
            "SELECT \n" +
            "    st_date, \n" +
            "    stYear, \n" +
            "    org_short_name, \n" +
            "    actualOutputQty, \n" +
            "    actualSalesQty, \n" +
            "    actualfootageqty, \n" +
            "    plannedFootageQty, \n" +
            "    plannedOutputQty, \n" +
            "    plannedSalesQty\n" +
            "FROM \n" +
            "    trend_data\n" +
            "ORDER BY \n" +
            "    stYear DESC, st_date DESC;   " +
            "</script>")
    List<CoalMiningTotalVo> getTrendProductionAndSaleByYear(@Param("year") Integer year,@Param("mineCode") String mineCode);



    @Select("<script>" +
            "     WITH data AS (\n" +
            "                SELECT\n" +
            "                    to_char(date_trunc('month', CAST(st_month <![CDATA[ || ]]> '-01' AS date)), 'MM') AS st_date,\n" +
            "                    to_char(date_trunc('month', CAST(st_month <![CDATA[ || ]]> '-01' AS date)), 'YYYY-MM') AS stYear,\n" +
            "              org_short_name AS org_short_name ,  \n" +
            "                    ROUND(actual_output_qty / 10000.0, 2) AS actualOutputQty,\n" +
            "                    ROUND(actual_sales_qty / 10000.0, 2) AS actualSalesQty," +
            " TO_CHAR(ROUND(actual_footage_qty   , 2), 'FM9999999990.00') AS actualfootageqty \n" +
            "                FROM\n" +
            "                    dm.dm_jygl_coal_mining_month_total_d_f\n" +
            "                WHERE\n" +
            "                    CAST(st_month <![CDATA[ || ]]> '-01' AS date) <![CDATA[ >= ]]> DATE_TRUNC('month', CURRENT_DATE) - INTERVAL '12 months'\n" +
            "                    AND CAST(st_month <![CDATA[ || ]]> '-01' AS date) <![CDATA[ < ]]> DATE_TRUNC('month', CURRENT_DATE)\n" +
            "                          and org_code = #{mineCode} " +
            "            )\n" +
            "            SELECT\n" +
            "                st_date,\n" +
            "                stYear,\n" +
            "                org_short_name,\n" +
            "                actualOutputQty,\n" +
            "                actualSalesQty," +
            " actualfootageqty \n" +
            "            FROM\n" +
            "                data\n" +
            "            ORDER BY\n" +
            "                stYear DESC; " +
            "</script>")
    List<CoalMiningTotalVo> recentlyTwelveMonthsByIndustry(@Param("flag") Integer flag,@Param("mineCode") String mineCode);




    @Select("<script>" +
            "      WITH data AS (\n" +
            "                SELECT\n" +
            "                   TO_CHAR(st_date, 'DD') AS st_date,  \n" +
            "                                 TO_CHAR(st_date, 'MM-DD') AS stYear, \n" +
            "                    org_short_name,\n" +
            "  TO_CHAR(ROUND(actual_output_qty / 10000.0, 2), 'FM9999999990.00') AS actualOutputQty ," +
            "                    TO_CHAR(ROUND(actual_sales_qty / 10000.0, 2), 'FM9999999990.00') AS actualSalesQty," +
            "" +
            " TO_CHAR(ROUND(actual_footage_qty   , 2), 'FM9999999990.00') AS actualfootageqty " +
            "                FROM\n" +
            "                    dm.dm_jygl_coal_mining_daily_total_d_f\n" +
            "                WHERE\n" +
            "                    st_date  <![CDATA[ >= ]]>  CURRENT_DATE - INTERVAL '30 days'\n" +
            "                          and org_code = #{mineCode} " +
            "                        <if test='mark ==1'>\n" +
            "                and st_date <![CDATA[ <= ]]>   current_date-1  \n" +
            "                        </if>\n" +
            "                        <if test='mark ==2'>\n" +
            "                and st_date <![CDATA[ <= ]]>   current_date-2  \n" +
            "                        </if>\n" +
            "            )\n" +
            "            SELECT\n" +
            "                st_date,\n" +
            "                stYear,\n" +
            "                org_short_name,\n" +
            "                actualOutputQty,\n" +
            "                actualSalesQty," +
            " actualfootageqty \n" +
            "            FROM\n" +
            "                data\n" +
            "            ORDER BY\n" +
            "                stYear DESC; " +
            "</script>")
    List<CoalMiningTotalVo> recentlyThirtyDaysByIndustry(@Param("flag") Integer flag,@Param("mark") Integer mark,@Param("mineCode") String mineCode);
}
